using System;
using System.Collections.Generic;
using System.Text;
using Entity;
using System.Data;
using System.Data.SqlClient;
using ECommerce.DAL;

namespace Entity
{
    [System.ComponentModel.DataObject]
    public class TblProductExtended : TblProduct
    {
        public static ECommerce.DAL.CustomCollection<TblProduct> GetNonDeletedProductsByBrand(int brandId, int currentPageIndex, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@BrandId", SqlDbType.Int, 0, ParameterDirection.Input, brandId);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_BY_BRAND);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static ECommerce.DAL.CustomCollection<TblProduct> GetActiveProductsByBrand(int brandId, int currentPageIndex, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@BrandId", SqlDbType.Int, 0, ParameterDirection.Input, brandId);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT_BY_BRAND);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static ECommerce.DAL.CustomCollection<TblProduct> GetActiveProductsByCategory(string orderby, int categoryId, int currentPageIndex, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderby);
            
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT_BY_CATEGORY);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static ECommerce.DAL.CustomCollection<TblProduct> GetNonDeletedProductsByCategory(int categoryId, int currentPageIndex, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_BY_CATEGORY);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static ECommerce.DAL.CustomCollection<TblProduct> GetAllProductsByBrand(int brandId, int currentPageIndex, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@BrandId", SqlDbType.Int, 0, ParameterDirection.Input, brandId);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ALL_PRODUCT_BY_BRAND);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static ECommerce.DAL.CustomCollection<TblProduct> GetAllProductsByCategory(int categoryId, int currentPageIndex, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ALL_PRODUCT_BY_CATEGORY);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static ECommerce.DAL.CustomCollection<TblProduct> GetNonDeletedProducts( int currentPageIndex, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static ECommerce.DAL.CustomCollection<TblProduct> GetActiveProducts( int currentPageIndex, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static ECommerce.DAL.CustomCollection<TblProduct> GetActiveSpecialProducts(int currentPageIndex, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ACTIVE_SPECIAL_PRODUCT);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static ECommerce.DAL.CustomCollection<TblProduct> GetActiveFeaturedProducts(int currentPageIndex, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ACTIVE_FEATURE_PRODUCT);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static int GetActiveFeaturedProductsCount()
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ACTIVE_FEATURE_PRODUCT_COUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static int GetAllProductsCountByCategory(int categoryId)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ALL_PRODUCT_COUNT_BY_CATEGORY);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static int GetNonDeletedProductsCountByCategory(int categoryId)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_COUNT_BY_CATEGORY);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static int GetActiveProductsCountByCategory(int categoryId)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT_COUNT_BY_CATEGORY);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static int GetAllProductsCountByBrand(int brandId)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@BrandId", SqlDbType.Int, 0, ParameterDirection.Input, brandId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ALL_PRODUCT_COUNT_BY_BRAND);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static int GetNonDeletedProductsCountByBrand(int brandId)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@BrandId", SqlDbType.Int, 0, ParameterDirection.Input, brandId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_COUNT_BY_BRAND);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static int GetActiveProductsCountByBrand(int brandId)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@BrandId", SqlDbType.Int, 0, ParameterDirection.Input, brandId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT_COUNT_BY_BRAND);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static int GetActiveProductsCount()
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT_COUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static int GetNonDeletedProductsCount()
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_COUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static int CountItemNumber(string itemNumber)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@ItemNumber", SqlDbType.NVarChar, 50, ParameterDirection.Input, itemNumber);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ITEM_NUMBER_COUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static int GetBackEndProductListCount(string productName, int categoryId, bool includeDeleted)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            StringBuilder condition = new StringBuilder();
            if (categoryId > 0)
            {
                condition.Append(" Where CategoryId = ").Append(categoryId.ToString()).Append(" ");
            }
            if (!string.IsNullOrEmpty(productName))
            {
                if (condition.Length > 0)
                    condition.Append(" AND ProductName LIKE '%").Append(string.IsNullOrEmpty(productName) ? "%" : productName).Append("%' ");
                else
                    condition.Append(" WHERE ProductName LIKE '%").Append(string.IsNullOrEmpty(productName) ? "%" : productName).Append("%' ");
            }
            if (!includeDeleted)
            {
                if (condition.Length > 0)
                    condition.Append(" AND IsDeleted = 0 ");
                else
                    condition.Append(" WHERE IsDeleted = 0 ");
            }

            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@condition", SqlDbType.NVarChar, 500, ParameterDirection.Input, condition.ToString());
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_BACKEND_PRODUCT_LIST_COUNT);

            int returnValue = Convert.ToInt32(ExecuteScalarCmd(sqlCmd)) ;
            return returnValue;
        }

        public static int GetBackEndProductListCountAdmin(string productName, string itemNumber, int categoryId, bool includeDeleted)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            StringBuilder condition = new StringBuilder();
            if (categoryId > 0)
            {
                condition.Append(" Where CategoryId = ").Append(categoryId.ToString()).Append(" ");
            }
            if (!string.IsNullOrEmpty(productName))
            {
                if (condition.Length > 0)
                    condition.Append(" AND ProductName LIKE '%").Append(string.IsNullOrEmpty(productName) ? "%" : productName).Append("%' ");
                else
                    condition.Append(" WHERE ProductName LIKE '%").Append(string.IsNullOrEmpty(productName) ? "%" : productName).Append("%' ");
            }
            if (!string.IsNullOrEmpty(itemNumber))
            {
                if (condition.Length > 0)
                    condition.Append(" AND ItemNumber LIKE '%").Append(string.IsNullOrEmpty(itemNumber) ? "%" : itemNumber).Append("%' ");
                else
                    condition.Append(" WHERE ItemNumber LIKE '%").Append(string.IsNullOrEmpty(itemNumber) ? "%" : itemNumber).Append("%' ");
            }
            if (!includeDeleted)
            {
                if (condition.Length > 0)
                    condition.Append(" AND IsDeleted = 0 ");
                else
                    condition.Append(" WHERE IsDeleted = 0 ");
            }

            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@condition", SqlDbType.NVarChar, 500, ParameterDirection.Input, condition.ToString());
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_BACKEND_PRODUCT_LIST_COUNT);

            int returnValue = Convert.ToInt32(ExecuteScalarCmd(sqlCmd));
            return returnValue;
        }

        public static CustomCollection<TblProduct> GetBackEndProductList(string productName, int categoryId, bool includeDeleted, string sortBy, int firstItem, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            StringBuilder condition = new StringBuilder();
            if (categoryId > 0 )
            {
                condition.Append(" Where CategoryId = ").Append(categoryId.ToString()).Append(" ");
            }
            if (!string.IsNullOrEmpty(productName))
            {
                if (condition.Length > 0)
                    condition.Append(" AND ProductName LIKE '%").Append(string.IsNullOrEmpty(productName)? "%" :productName).Append("%' ");
                else
                    condition.Append(" WHERE ProductName LIKE '%").Append(string.IsNullOrEmpty(productName) ? "%" : productName).Append("%' ");
            }
            if (!includeDeleted)
            {
                if (condition.Length > 0)
                    condition.Append(" AND IsDeleted = 0 ");
                else
                    condition.Append(" WHERE IsDeleted = 0 ");
            }

            AddParameterToSqlCmd(sqlCmd, "@Condition", SqlDbType.NVarChar, 500, ParameterDirection.Input, condition.ToString());
            AddParameterToSqlCmd(sqlCmd, "@Orderby", SqlDbType.NVarChar, 500, ParameterDirection.Input, sortBy);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, firstItem);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_BACKEND_PRODUCT_LIST);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static CustomCollection<TblProduct> GetBackEndProductListAdmin(string productName, string itemNumber, int categoryId, bool includeDeleted, string sortBy, int firstItem, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            StringBuilder condition = new StringBuilder();
            if (categoryId > 0)
            {
                condition.Append(" Where CategoryId = ").Append(categoryId.ToString()).Append(" ");
            }
            if (!string.IsNullOrEmpty(productName))
            {
                if (condition.Length > 0)
                    condition.Append(" AND ProductName LIKE '%").Append(string.IsNullOrEmpty(productName) ? "%" : productName).Append("%' ");
                else
                    condition.Append(" WHERE ProductName LIKE '%").Append(string.IsNullOrEmpty(productName) ? "%" : productName).Append("%' ");
            }
            if (!string.IsNullOrEmpty(itemNumber))
            {
                if (condition.Length > 0)
                    condition.Append(" AND ItemNumber LIKE '%").Append(string.IsNullOrEmpty(itemNumber) ? "%" : itemNumber).Append("%' ");
                else
                    condition.Append(" WHERE ItemNumber LIKE '%").Append(string.IsNullOrEmpty(itemNumber) ? "%" : itemNumber).Append("%' ");
            }
            if (!includeDeleted)
            {
                if (condition.Length > 0)
                    condition.Append(" AND IsDeleted = 0 ");
                else
                    condition.Append(" WHERE IsDeleted = 0 ");
            }

            AddParameterToSqlCmd(sqlCmd, "@Condition", SqlDbType.NVarChar, 500, ParameterDirection.Input, condition.ToString());
            AddParameterToSqlCmd(sqlCmd, "@Orderby", SqlDbType.NVarChar, 500, ParameterDirection.Input, sortBy);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, firstItem);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_BACKEND_PRODUCT_LIST);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static bool TemporaryDeleteProducts(List<int> deleteIds)
        {
            //Validate Input
            foreach (Int32 productId in deleteIds)
                if (productId <= GetProductIdMinValue)
                    throw (new ArgumentOutOfRangeException("productId"));

            //performing deletion operation //

            string xmlString = FormatXMLForIdArray(deleteIds);

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@ProductIdList", SqlDbType.Xml, xmlString.ToString().Length, ParameterDirection.Input, xmlString.ToString());
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCT_TEMPORARY_DELETE_PRODUCTS);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == deleteIds.Count ? true : false);
        }

        public static CustomCollection<TblProduct> GetFeaturedProducts(int howMany)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@HowMany", SqlDbType.Int, 0, ParameterDirection.Input, howMany);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_FEATURED_PRODUCT);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }
        public static CustomCollection<TblProduct> GetRelatedProducts(int howMany,int categoryId,int productId)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@HowMany", SqlDbType.Int, 0, ParameterDirection.Input, howMany);
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            AddParameterToSqlCmd(sqlCmd, "@ProductId", SqlDbType.Int, 0, ParameterDirection.Input, productId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_RELATED_PRODUCT);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static CustomCollection<TblProduct> GetAllFeaturedProducts(int howMany)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@HowMany", SqlDbType.Int, 0, ParameterDirection.Input, howMany);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_NONDELETED_FEATURED_PRODUCT);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static CustomCollection<TblProduct> GetProductByKeywordAndItemNumber(string keyword, string itemNumber)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ProductName", SqlDbType.NVarChar, 500, ParameterDirection.Input, keyword);
            AddParameterToSqlCmd(sqlCmd, "@ItemNumber", SqlDbType.NVarChar, 500, ParameterDirection.Input, itemNumber);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_PRODUCT_BY_KEYWORD_AND_ITEMNUMBER);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }
        public static CustomCollection<TblProduct> GetProductByBothName(int currentPageIndex, int pageSize, string keyword, string itemNumber)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ProductName", SqlDbType.NVarChar, 500, ParameterDirection.Input, keyword);
            AddParameterToSqlCmd(sqlCmd, "@ItemNumber", SqlDbType.NVarChar, 500, ParameterDirection.Input, itemNumber);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, "sp_TblProduct_Get_Product_By_Both_Name");
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }
        public static int GetProductByBothNameCount(string keyword, string itemNumber)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ProductName", SqlDbType.NVarChar, 500, ParameterDirection.Input, keyword);
            AddParameterToSqlCmd(sqlCmd, "@ItemNumber", SqlDbType.NVarChar, 500, ParameterDirection.Input, itemNumber);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, "sp_TblProduct_Get_Product_By_Both_Name_Count");
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }
        public static CustomCollection<TblProduct> GetProductByPrice(string minPrice, string maxPrice)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            if (!String.IsNullOrEmpty(minPrice) && !String.IsNullOrEmpty(maxPrice))
            {
                AddParameterToSqlCmd(sqlCmd, "@MinPrice", SqlDbType.NVarChar, 500, ParameterDirection.Input, minPrice);
                AddParameterToSqlCmd(sqlCmd, "@MaxPrice", SqlDbType.NVarChar, 500, ParameterDirection.Input, maxPrice);

                SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_PRODUCT_BY_PRICE);
            }
            else if (!String.IsNullOrEmpty(minPrice) && String.IsNullOrEmpty(maxPrice))
            {
                AddParameterToSqlCmd(sqlCmd, "@MinPrice", SqlDbType.NVarChar, 500, ParameterDirection.Input, minPrice);
                
                SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_PRODUCT_BY_MIN_PRICE);
            }
            else if (String.IsNullOrEmpty(minPrice) && !String.IsNullOrEmpty(maxPrice))
            {
                AddParameterToSqlCmd(sqlCmd, "@MaxPrice", SqlDbType.NVarChar, 500, ParameterDirection.Input, maxPrice);
                
                SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_PRODUCT_BY_MAX_PRICE);
            }
            
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static CustomCollection<TblProduct> GetProductByPrice(string minPrice, string maxPrice, string orderby, int currentPageIndex, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderby);

            if (!String.IsNullOrEmpty(minPrice) && !String.IsNullOrEmpty(maxPrice))
            {
                AddParameterToSqlCmd(sqlCmd, "@MinPrice", SqlDbType.NVarChar, 500, ParameterDirection.Input, minPrice);
                AddParameterToSqlCmd(sqlCmd, "@MaxPrice", SqlDbType.NVarChar, 500, ParameterDirection.Input, maxPrice);

                SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_PRODUCT_BY_PRICE_PAGED);
            }
            else if (!String.IsNullOrEmpty(minPrice) && String.IsNullOrEmpty(maxPrice))
            {
                AddParameterToSqlCmd(sqlCmd, "@MinPrice", SqlDbType.NVarChar, 500, ParameterDirection.Input, minPrice);

                SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_PRODUCT_BY_MIN_PRICE_PAGED);
            }
            else if (String.IsNullOrEmpty(minPrice) && !String.IsNullOrEmpty(maxPrice))
            {
                AddParameterToSqlCmd(sqlCmd, "@MaxPrice", SqlDbType.NVarChar, 500, ParameterDirection.Input, maxPrice);

                SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_PRODUCT_BY_MAX_PRICE_PAGED);
            }
            
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static CustomCollection<TblProduct> GetProductsByItemNumberPrefix(string prefix, int howMany)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@Prefix", SqlDbType.NVarChar, 500, ParameterDirection.Input, prefix);
            AddParameterToSqlCmd(sqlCmd, "@HowMany", SqlDbType.Int, 0, ParameterDirection.Input, howMany);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_PRODUCT_BY_ITEMNUMBER_PREFIX);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }
        public static CustomCollection<TblProduct> GetProductByExactName(string productName)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@ProductName", SqlDbType.NVarChar, productName.Length, ParameterDirection.Input, productName);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, "sp_TblProduct_Get_Product_By_ProductName");
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static TblProduct GetProductByItemNumber(string itemNumber)
        {
            if (string.IsNullOrEmpty(itemNumber))
                itemNumber = "%";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ItemNumber", SqlDbType.NVarChar, 500, ParameterDirection.Input, itemNumber);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCT_GET_PRODUCT_BY_ITEM_NUMBER);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }

        public static CustomCollection<TblProduct> GetNonDeletedProductsByItemNumberPrefix(string prefix, int howMany)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@Prefix", SqlDbType.NVarChar, 500, ParameterDirection.Input, prefix);
            AddParameterToSqlCmd(sqlCmd, "@HowMany", SqlDbType.Int, 0, ParameterDirection.Input, howMany);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_BY_ITEMNUMBER_PREFIX);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static TblProduct GetNonDeletedProductByItemNumber(string itemNumber)
        {
            if (string.IsNullOrEmpty(itemNumber))
                itemNumber = "%";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ItemNumber", SqlDbType.NVarChar, 500, ParameterDirection.Input, itemNumber);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCT_GET_NON_DELETED_PRODUCT_BY_ITEM_NUMBER);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }

        public static CustomCollection<TblProduct> GetProductListForFavorites()
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_PRODUCT_FOR_FAVORITES);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }
        public static CustomCollection<TblProduct> GetProductListSpecials()
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_PRODUCT_FOR_SPECIALS);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static CustomCollection<TblProduct> GetNonDeletedProductListForFavorites()
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_FOR_FAVORITES);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static bool RemoveFavorites(List<int> updateIds)
        {
            //Validate Input
            foreach (Int32 productId in updateIds)
                if (productId <= GetProductIdMinValue)
                    throw (new ArgumentOutOfRangeException("productId"));

            //performing deletion operation //

            string xmlString = FormatXMLForIdArray(updateIds);

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@ProductIdList", SqlDbType.Xml, xmlString.ToString().Length, ParameterDirection.Input, xmlString.ToString());
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCT_UPDATE_FAVORITE_PRODUCTLIST);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == updateIds.Count ? true : false);
        }

        public static ECommerce.DAL.CustomCollection<TblProduct> GetBackendProductListByCondition(string condition, string sortBy, int firstItem, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@Condition", SqlDbType.NVarChar, 2000, ParameterDirection.Input, condition);
            AddParameterToSqlCmd(sqlCmd, "@Orderby", SqlDbType.NVarChar, 500, ParameterDirection.Input, sortBy);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, firstItem);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_LIST_BY_CONDITION);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static ECommerce.DAL.CustomCollection<TblProduct> GetBackendProductPurchaseProductListBySoldDate(DateTime fromdate, DateTime todate)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@fromdate", SqlDbType.DateTime, 0, ParameterDirection.Input, fromdate);
            AddParameterToSqlCmd(sqlCmd, "@todate", SqlDbType.DateTime, 0, ParameterDirection.Input, todate);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_PURCHASE_GET_PRODUCTS);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static ECommerce.DAL.CustomCollection<TblProduct> GetProductByCategoryURL(string categoryURL)
        {
            if (categoryURL.EndsWith("/"))
                categoryURL = "~/" + categoryURL;
            else
                categoryURL = "~/" + categoryURL + "/";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryUrl", SqlDbType.NVarChar, categoryURL.Length, ParameterDirection.Input, categoryURL);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, "spr_TblProduct_GetTblProductByCategoryURL");
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection;
            else
                return null;
        }


        private const string SP_TBL_PRODUCT_PURCHASE_GET_PRODUCTS = "sp_get_all_products_by_sale_date";

        private const string SP_TBL_PRODUCT_GET_ACTIVE_SPECIAL_PRODUCT = "sp_TblProduct_Get_Active_Special_Product";
        private const string SP_TBL_PRODUCT_GET_ACTIVE_FEATURE_PRODUCT = "sp_TblProduct_Get_Active_Featured_Product";
        private const string SP_TBL_PRODUCT_GET_ACTIVE_FEATURE_PRODUCT_COUNT = "sp_TblProduct_Get_Active_Featured_Product_Count";

        private const string SP_TBL_PRODUCT_GET_PRODUCT_FOR_SPECIALS = "sp_tbl_product_get_product_for_specials";
        private const string SP_TBL_PRODUCT_GET_LIST_BY_CONDITION = "sp_tbl_product_get_list_by_condition";
        private const string SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_BY_BRAND = "sp_TblProduct_Get_Non_Deleted_Product_By_Brand";
        private const string SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT_BY_BRAND = "sp_TblProduct_Get_Active_Product_By_Brand";
        private const string SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT_BY_CATEGORY = "sp_TblProduct_Get_Active_Product_By_Category";
        private const string SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_BY_CATEGORY = "sp_TblProduct_Get_Non_Deleted_Product_By_Category";
        private const string SP_TBL_PRODUCT_GET_ALL_PRODUCT_BY_BRAND = "sp_TblProduct_Get_All_Product_By_Brand";
        private const string SP_TBL_PRODUCT_GET_ALL_PRODUCT_BY_CATEGORY = "sp_TblProduct_Get_All_Product_By_Category";
        private const string SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT = "sp_TblProduct_Get_Non_Deleted_Product";
        private const string SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT = "sp_TblProduct_Get_Active_Product";
        private const string SP_TBL_PRODUCT_GET_ALL_PRODUCT_COUNT_BY_CATEGORY = "sp_TblProduct_Get_All_Product_Count_By_Category";
        private const string SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_COUNT_BY_CATEGORY = "sp_TblProduct_Get_Non_Deleted_Product_Count_By_Category";
        private const string SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT_COUNT_BY_CATEGORY = "sp_TblProduct_Get_Active_Product_Count_By_Category";
        private const string SP_TBL_PRODUCT_GET_ALL_PRODUCT_COUNT_BY_BRAND = "sp_TblProduct_Get_All_Product_Count_By_Brand";
        private const string SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_COUNT_BY_BRAND = "sp_TblProduct_Get_Non_Deleted_Product_Count_By_Brand";
        private const string SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT_COUNT_BY_BRAND = "sp_TblProduct_Get_Active_Product_Count_By_Brand";
        private const string SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT_COUNT = "sp_TblProduct_Get_Active_Product_Count";
        private const string SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_COUNT = "sp_TblProduct_Get_Non_Deleted_Product_Count";
        private const string SP_TBL_PRODUCT_GET_ITEM_NUMBER_COUNT = "sp_TblProduct_Get_ItemNumber_Count";
        private const string SP_TBL_PRODUCT_GET_BACKEND_PRODUCT_LIST_COUNT = "sp_TblProduct_Get_Backend_Product_List_Count";
        private const string SP_TBL_PRODUCT_GET_BACKEND_PRODUCT_LIST = "sp_TblProduct_Get_Backend_Product_List";
        private const string SP_TBLPRODUCT_TEMPORARY_DELETE_PRODUCTS = "sp_TblProduct_Temporary_Delete_Products";
        private const string SP_TBL_PRODUCT_GET_FEATURED_PRODUCT = "sp_TblProduct_Get_Featured_Product";
        private const string SP_TBL_PRODUCT_GET_RELATED_PRODUCT = "sp_TblProduct_Get_Related_Product";
        private const string SP_TBL_PRODUCT_GET_NONDELETED_FEATURED_PRODUCT = "sp_TblProduct_Get_NonDeletedFeatured_Product";
        private const string SP_TBL_PRODUCT_GET_PRODUCT_BY_KEYWORD_AND_ITEMNUMBER = "sp_TblProduct_Get_Product_By_Keyword_And_ItemNumber";
        private const string SP_TBLPRODUCT_GET_PRODUCT_BY_ITEM_NUMBER = "sp_TblProduct_Get_Product_By_Item_Number";
        private const string SP_TBL_PRODUCT_GET_PRODUCT_BY_ITEMNUMBER_PREFIX = "sp_TblProduct_Get_Product_By_ItemNumber_Prefix";
        private const string SP_TBLPRODUCT_GET_NON_DELETED_PRODUCT_BY_ITEM_NUMBER = "sp_TblProduct_Get_NonDeleted_Product_By_Item_Number";
        private const string SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_BY_ITEMNUMBER_PREFIX = "sp_TblProduct_Get_NonDeleted_Product_By_ItemNumber_Prefix";
        private const string SP_TBL_PRODUCT_GET_PRODUCT_FOR_FAVORITES = "sp_TblProduct_Get_Product_For_Favorites";
        private const string SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_FOR_FAVORITES = "sp_TblProduct_Get_NonDeleted_Product_For_Favorites";
        private const string SP_TBLPRODUCT_UPDATE_FAVORITE_PRODUCTLIST = "sp_TblProduct_Update_Favorite_ProductList";

        private const string SP_TBL_PRODUCT_GET_PRODUCT_BY_PRICE = "sp_TblProduct_Get_Product_By_Price";
        private const string SP_TBL_PRODUCT_GET_PRODUCT_BY_MIN_PRICE = "sp_TblProduct_Get_Product_By_Min_Price";
        private const string SP_TBL_PRODUCT_GET_PRODUCT_BY_MAX_PRICE = "sp_TblProduct_Get_Product_By_Max_Price";

        private const string SP_TBL_PRODUCT_GET_PRODUCT_BY_PRICE_PAGED = "sp_TblProduct_Get_Product_By_Price_Paged";
        private const string SP_TBL_PRODUCT_GET_PRODUCT_BY_MIN_PRICE_PAGED = "sp_TblProduct_Get_Product_By_Min_Price_Paged";
        private const string SP_TBL_PRODUCT_GET_PRODUCT_BY_MAX_PRICE_PAGED = "sp_TblProduct_Get_Product_By_Max_Price_Paged";
    }
}
